package model.dao;

import java.sql.*;
import java.util.ArrayList;

import model.bean.DongVatModel;
import model.bean.ThucVatModel;

public class ProcessDAO {
	private Connection con;
	private Statement st;

	// connect database
	public void connect() {
		try {
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5050/culaocham", "postgres","07051990");
		} catch (ClassNotFoundException e) {
			System.out.println("Not found PostgreSQL JDBC Driver");
			e.printStackTrace();
			return;
		} catch (Exception e) {
			System.out.println("connect database failed");
		}
		System.out.println("Connected Database");
	}

	// close connect
	public void closeConnect() throws SQLException {
		con.close();
		st.close();
		System.out.println("Closed connect");
	}

	// check login account
	public boolean checkLogin(String user, String password) throws SQLException {
		connect();
		try {
            st = con.createStatement();
            String sql = "SELECT username, password FROM account";
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()){
            	if(user.equals(rs.getString("username")))
     				return true;
            }
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
		return false;
	}
	
	public void dangKyTaiKhoan(String hoTenDK, String ngaySinh, String tenDangNhap, String matKhauDK) throws SQLException{
		connect();
		try {
			 st = con.createStatement();
			 String sql = "INSERT INTO account VALUES ('"+ tenDangNhap +"', '"+ matKhauDK +"', '"+ hoTenDK +"', '"+ ngaySinh +"')";
			 st.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public ArrayList<DongVatModel> listInfoDongVat() throws SQLException{
		connect();
		ArrayList<DongVatModel> listInfoDongVat = new ArrayList<DongVatModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT row_number() over (order by dv.id) as stt, count(dv.id) as soluong , gioi, nganh, lop, bo, ho, chi, loai, "
				+"ns.trencan, ns.trencay, ns.duoinuoc, dacdiem, thucan FROM dongvat dv INNER JOIN dv_noisong ns ON dv.noisong = ns.id "
				+"INNER JOIN dv_vitri vt ON dv.id = vt.id GROUP BY dv.id, ns.trencan, ns.trencay, ns.duoinuoc";
			ResultSet rs = st.executeQuery(sql);
			String noisong;
			while(rs.next()){
				DongVatModel dongVatModel = new DongVatModel();
				noisong = "";
				
				dongVatModel.setDv_stt(rs.getInt("stt"));
				dongVatModel.setDv_soLuong(rs.getInt("soluong"));
				dongVatModel.setDv_gioi(rs.getString("gioi"));
				dongVatModel.setDv_nganh(rs.getString("nganh"));
				dongVatModel.setDv_lop(rs.getString("lop"));
				dongVatModel.setDv_bo(rs.getString("bo"));
				dongVatModel.setDv_ho(rs.getString("ho"));
				dongVatModel.setDv_chi(rs.getString("chi"));
				dongVatModel.setDv_loai(rs.getString("loai"));
				if(rs.getBoolean("trencan") == true) noisong += "Trên cạn, ";
				if(rs.getBoolean("trencay") == true) noisong += "Trên cây, ";
				if(rs.getBoolean("duoinuoc") == true) noisong += "Dưới nước, ";
				dongVatModel.setDv_noiSong(noisong);
				dongVatModel.setDv_dacDiem(rs.getString("dacdiem"));
				dongVatModel.setDv_thucAn(rs.getString("thucan"));
				
				listInfoDongVat.add(dongVatModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listInfoDongVat;
	}
	
	public ArrayList<ThucVatModel> listInfoThucVat() throws SQLException{
		connect();
		ArrayList<ThucVatModel> listInfoThucVat = new ArrayList<ThucVatModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT row_number() over (order by tv.id) as stt, count(tv.id) as soluong, tv.loai, tv.ho, cc.loai as chieucao, "
				+"tv.duongkinh, dtc.dongtay, dtc.nambac, tv.loaisinhtruong, gt.go, gt.canh, gt.thuoc, gt.vlxaydung, gt.thucongmynghe, "
				+"gt.anduoc, gt.caydoc, gt.chotindau, gt.nhua, gt.soi, gt.thuocnhuom, gt.congdungkhac, tv.dangsong, tv.dacdiem"
				+" FROM thucvat tv INNER JOIN tv_chieucao cc ON tv.chieucao = cc.id INNER JOIN tv_dotanche dtc ON tv.dotanche = dtc.id"
				+" INNER JOIN tv_giatri gt ON tv.giatri = gt.id INNER JOIN tv_vitri vt ON tv.id = vt.id GROUP BY tv.id, tv.loai,tv.ho,"
				+" cc.loai,tv.duongkinh, dtc.dongtay, dtc.nambac, tv.loaisinhtruong, gt.go, gt.canh, gt.thuoc, gt.vlxaydung,"
				+" gt.thucongmynghe, gt.anduoc, gt.caydoc, gt.chotindau, gt.nhua, gt.soi, gt.thuocnhuom, gt.congdungkhac, tv.dangsong, tv.dacdiem";
			ResultSet rs = st.executeQuery(sql);
			String giatri;
			while(rs.next()){
				ThucVatModel thucVatModel = new ThucVatModel();
				giatri = "";
				
				thucVatModel.setTv_stt(rs.getInt("stt"));
				thucVatModel.setTv_soLuong(rs.getInt("soluong"));
				thucVatModel.setTv_loai(rs.getString("loai"));
				thucVatModel.setTv_ho(rs.getString("ho"));
				thucVatModel.setTv_chieuCao(rs.getString("chieucao"));
				thucVatModel.setTv_duongKinh(rs.getString("duongkinh"));
				thucVatModel.setTv_doTanChe(rs.getString("dongtay") +"m, "+ rs.getString("nambac") + "m");
				thucVatModel.setTv_loaiSinhTruong(rs.getString("loaisinhtruong"));
				if(rs.getBoolean("go") == true) giatri += "Gỗ, ";
				if(rs.getBoolean("canh") == true) giatri += "Cảnh, ";
				if(rs.getBoolean("thuoc") == true) giatri += "Thuốc, ";
				if(rs.getBoolean("vlxaydung") == true) giatri += "Vật liệu xây dựng, ";
				if(rs.getBoolean("thucongmynghe") == true) giatri += "Thủ công mỹ nghệ, ";
				if(rs.getBoolean("anduoc") == true) giatri += "Ăn được, ";
				if(rs.getBoolean("caydoc") == true) giatri += "Cây độc, ";
				if(rs.getBoolean("chotindau") == true) giatri += "Cho tinh dầu, ";
				if(rs.getBoolean("nhua") == true) giatri += "Nhựa, ";
				if(rs.getBoolean("soi") == true) giatri += "Sợi, ";
				if(rs.getBoolean("thuocnhuom") == true) giatri += "Thuốc nhuộm, ";
				if(rs.getBoolean("congdungkhac") == true) giatri += "Công dụng khác, ";
				thucVatModel.setTv_giaTri(giatri);
				thucVatModel.setTv_dangSong(rs.getString("dangsong"));
				thucVatModel.setTv_dacDiem(rs.getString("dacdiem"));
				
				listInfoThucVat.add(thucVatModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally {
			closeConnect();
		}
		return listInfoThucVat;
	}
	
	public ArrayList<ThucVatModel> listPoinThucVat() throws SQLException{
		connect();
		ArrayList<ThucVatModel> listPoinThucVat = new ArrayList<ThucVatModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT row_number() over (order by tv.id) as stt, tv.id, tv.loai, tv.ho, cc.loai as chieucao,"
				+" tv.duongkinh, dtc.dongtay, dtc.nambac, tv.loaisinhtruong, gt.go, gt.canh, gt.thuoc, gt.vlxaydung,"
				+" gt.thucongmynghe, gt.anduoc, gt.caydoc, gt.chotindau, gt.nhua, gt.soi, gt.thuocnhuom, gt.congdungkhac,"
				+" tv.dangsong, tv.dacdiem, tv.image, ST_X(vt.vitri) as longitude, ST_Y(vt.vitri) as latitude"
				+" FROM thucvat tv INNER JOIN tv_chieucao cc ON tv.chieucao = cc.id INNER JOIN tv_dotanche dtc ON"
				+" tv.dotanche = dtc.id INNER JOIN tv_giatri gt ON tv.giatri = gt.id INNER JOIN tv_vitri vt ON tv.id = vt.id";
			ResultSet rs = st.executeQuery(sql);
			String giatri;
			while(rs.next()){
				ThucVatModel thucVatModel = new ThucVatModel();
				giatri = "";
				
				thucVatModel.setTv_stt(rs.getInt("stt"));
				thucVatModel.setTv_id(rs.getInt("id"));
				thucVatModel.setTv_loai(rs.getString("loai"));
				thucVatModel.setTv_ho(rs.getString("ho"));
				thucVatModel.setTv_chieuCao(rs.getString("chieucao"));
				thucVatModel.setTv_duongKinh(rs.getString("duongkinh"));
				thucVatModel.setTv_doTanChe(rs.getString("dongtay") +"m, "+ rs.getString("nambac") + "m");
				thucVatModel.setTv_loaiSinhTruong(rs.getString("loaisinhtruong"));
				if(rs.getBoolean("go") == true) giatri += "Gỗ, ";
				if(rs.getBoolean("canh") == true) giatri += "Cảnh, ";
				if(rs.getBoolean("thuoc") == true) giatri += "Thuốc, ";
				if(rs.getBoolean("vlxaydung") == true) giatri += "Vật liệu xây dựng, ";
				if(rs.getBoolean("thucongmynghe") == true) giatri += "Thủ công mỹ nghệ, ";
				if(rs.getBoolean("anduoc") == true) giatri += "Ăn được, ";
				if(rs.getBoolean("caydoc") == true) giatri += "Cây độc, ";
				if(rs.getBoolean("chotindau") == true) giatri += "Cho tinh dầu, ";
				if(rs.getBoolean("nhua") == true) giatri += "Nhựa, ";
				if(rs.getBoolean("soi") == true) giatri += "Sợi, ";
				if(rs.getBoolean("thuocnhuom") == true) giatri += "Thuốc nhuộm, ";
				if(rs.getBoolean("congdungkhac") == true) giatri += "Công dụng khác, ";
				thucVatModel.setTv_giaTri(giatri);
				thucVatModel.setTv_dangSong(rs.getString("dangsong"));
				thucVatModel.setTv_dacDiem(rs.getString("dacdiem"));
				thucVatModel.setTv_image(rs.getString("image"));
				thucVatModel.setTv_viTri_long(rs.getDouble("longitude"));
				thucVatModel.setTv_viTri_lat(rs.getDouble("latitude"));
				
				listPoinThucVat.add(thucVatModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally {
			closeConnect();
		}
		return listPoinThucVat;
	}
	public ArrayList<DongVatModel> listPoinDongVat() throws SQLException{
		connect();
		ArrayList<DongVatModel> listPoinDongVat = new ArrayList<DongVatModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT row_number() over (order by dv.id) as stt, dv.id, gioi, nganh, lop, bo, ho, chi, loai,"
				+" ns.trencan, ns.trencay, ns.duoinuoc, dacdiem, thucan, image, ST_X(vt.vitri) as longitude, ST_Y(vt.vitri) as latitude "
				+ "FROM dongvat dv INNER JOIN dv_noisong ns ON dv.noisong = ns.id INNER JOIN dv_vitri vt ON dv.id = vt.id";
			ResultSet rs = st.executeQuery(sql);
			String noisong;
			while(rs.next()){
				DongVatModel dongVatModel = new DongVatModel();
				noisong = "";
				
				dongVatModel.setDv_stt(rs.getInt("stt"));
				dongVatModel.setDv_id(rs.getInt("id"));
				dongVatModel.setDv_gioi(rs.getString("gioi"));
				dongVatModel.setDv_nganh(rs.getString("nganh"));
				dongVatModel.setDv_lop(rs.getString("lop"));
				dongVatModel.setDv_bo(rs.getString("bo"));
				dongVatModel.setDv_ho(rs.getString("ho"));
				dongVatModel.setDv_chi(rs.getString("chi"));
				dongVatModel.setDv_loai(rs.getString("loai"));
				if(rs.getBoolean("trencan") == true) noisong += "Trên cạn, ";
				if(rs.getBoolean("trencay") == true) noisong += "Trên cây, ";
				if(rs.getBoolean("duoinuoc") == true) noisong += "Dưới nước, ";
				dongVatModel.setDv_noiSong(noisong);
				dongVatModel.setDv_dacDiem(rs.getString("dacdiem"));
				dongVatModel.setDv_thucAn(rs.getString("thucan"));
				dongVatModel.setDv_image(rs.getString("image"));
				dongVatModel.setDv_viTri_long(rs.getDouble("longitude"));
				dongVatModel.setDv_viTri_lat(rs.getDouble("latitude"));
				
				listPoinDongVat.add(dongVatModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listPoinDongVat;
	}
	
	public void addThucvat(String tvloai, String tvho,
			String tvchieucao, String tvduongkinh, String tvloaisinhtruong,
			String tvdangsong, String tvdongtay, String tvnambac,
			double tvlatatude, double tvlongatude, boolean tvgtgo,
			boolean tvgtcanh, boolean tvgtthuoc, boolean tvgtvlxaydung,
			boolean tvgtthucongmynghe, boolean tvgtanduoc, boolean tvgtcaydoc,
			boolean tvgtchotinhdau, boolean tvgtnhua, boolean tvgtsoi,
			boolean tvgthuocnhuom, boolean tvgtcongdungkhac, String tvdacdiem, String tvimage)
			throws SQLException {
		connect();
		try {
            st = con.createStatement();
            String sqlDoTanChe = "INSERT INTO tv_dotanche VALUES(default,"+ Integer.parseInt(tvdongtay.trim()) +","+ Integer.parseInt(tvnambac.trim()) +")";
			String sqlGiaTri = "INSERT INTO tv_giatri VALUES (default,"
					+ tvgtgo + "," + tvgtcanh + "," + tvgtthuoc + ","
					+ tvgtvlxaydung + "," + tvgtthucongmynghe + ","
					+ tvgtanduoc + ", " + tvgtcaydoc + "," + tvgtchotinhdau
					+ "," + tvgtnhua + "," + tvgtsoi + "," + tvgthuocnhuom
					+ "," + tvgtcongdungkhac + ")";
			int chieucao = 0;
			if("Cây tái sinh".equals(tvchieucao)) chieucao = 1;
			else if("Cây bụi".equals(tvchieucao)) chieucao = 2;
			else if("Dưới tán".equals(tvchieucao)) chieucao = 3;
			else if("Tán".equals(tvchieucao)) chieucao = 4;
			else if("Trên tán".equals(tvchieucao)) chieucao = 5;
			String sqlThucVat = "INSERT INTO thucvat VALUES(default,'" + tvloai
					+ "','" + tvho + "'," + chieucao + ",'" + tvduongkinh
					+ "',(SELECT MAX(id) FROM tv_dotanche),'" + tvloaisinhtruong
					+ "',(SELECT MAX(id) FROM tv_giatri),'" + tvdangsong + "','"+ tvdacdiem + "', '"+ tvimage + "')";
			String sqlvitri = "INSERT INTO tv_vitri VALUES (default,(SELECT MAX(id) FROM thucvat), ST_SetSRID(ST_MakePoint('"+ tvlongatude +"', '"+ tvlatatude +"'), 4326))";
            st.executeUpdate(sqlDoTanChe);
            st.executeUpdate(sqlGiaTri);
            st.executeUpdate(sqlThucVat);
            st.executeUpdate(sqlvitri);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void addDongvat(String dvloai, String dvho,
			String dvgioi, String dvnganh, String dvlop,
			String dvbo, String dvchi, String dvthucan, String dvdacdiem,
			double dvlatatude, double dvlongatude, boolean dvnstrencan,
			boolean dvnstrencay, boolean dvnsduoinuoc, String dvimage)
			throws SQLException {
		connect();
		try {
            st = con.createStatement();
            String sqlNoiSong = "INSERT INTO dv_noisong VALUES(default,"+ dvnstrencan +","+ dvnstrencay +","+ dvnsduoinuoc +" )";
			String sqlDongVat = "INSERT INTO dongvat VALUES(default,'" + dvgioi
					+ "','" + dvnganh + "','" + dvlop + "','" + dvbo + "','" + dvho
					+ "','" + dvchi + "','"+ dvloai + "',(SELECT MAX(id) FROM dv_noisong),'"+ dvdacdiem + "','"+ dvthucan + "','"+ dvimage + "')";
			String sqlvitri = "INSERT INTO dv_vitri VALUES (default,(SELECT MAX(id) FROM dongvat), ST_SetSRID(ST_MakePoint('"+ dvlongatude +"', '"+ dvlatatude +"'), 4326))";
            st.executeUpdate(sqlNoiSong);
            st.executeUpdate(sqlDongVat);
            st.executeUpdate(sqlvitri);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void addViTriThucVat(int tvid, double tvlatatude, double tvlongatude)  throws SQLException{
		connect();
		try {
            st = con.createStatement();
            String sql = "INSERT INTO tv_vitri VALUES (default,"+ tvid +",ST_SetSRID(ST_MakePoint('"+ tvlongatude +"', '"+ tvlatatude +"'), 4326))";
            st.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void addViTriDongVat(int dvid, double dvlatatude, double dvlongatude)  throws SQLException{
		connect();
		try {
            st = con.createStatement();
            String sql = "INSERT INTO dv_vitri VALUES (default,"+ dvid +",ST_SetSRID(ST_MakePoint('"+ dvlongatude +"', '"+ dvlatatude +"'), 4326))";
            st.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void deleteThucVat(double tvlatatude, double tvlongatude, int tvid, String tvho) throws SQLException{
		connect();
		try {
            st = con.createStatement();
            if(tvlatatude != 0){
            	//xoa 1 ca the thuc vat
				String sqlDelCaThe = "DELETE FROM tv_vitri WHERE vitri = (ST_SetSRID(ST_MakePoint('"+ tvlongatude +"', '"+ tvlatatude +"'), 4326))";
					st.executeUpdate(sqlDelCaThe);
            } else if(tvlatatude == 0 && tvid != 0){
            	//xoa loai thuc vat
            	String sqlDelLoai;
            	sqlDelLoai = "DELETE FROM tv_vitri WHERE id = "+ tvid +"";
            		st.executeUpdate(sqlDelLoai);
            	sqlDelLoai = "DELETE FROM thucvat WHERE id = "+ tvid +"";
            		st.executeUpdate(sqlDelLoai);
            } else {
            	//xoa ho thuc vat
            	String sqlDelHo;
            	sqlDelHo = "DELETE FROM tv_vitri WHERE id IN (SELECT id FROM thucvat WHERE ho like '"+ tvho +"')";
            		st.executeUpdate(sqlDelHo);
            	sqlDelHo = "DELETE FROM thucvat WHERE id IN (SELECT id FROM thucvat WHERE ho like '"+ tvho +"')";
            		st.executeUpdate(sqlDelHo);
            }
            
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void deleteDongVat(double dvlatatude, double dvlongatude, int dvid, String dvho) throws SQLException{
		connect();
		try {
            st = con.createStatement();
            if(dvlatatude != 0){
            	//xoa 1 ca the dong vat
				String sqlDelCaThe = "DELETE FROM dv_vitri WHERE vitri = (ST_SetSRID(ST_MakePoint('"+ dvlongatude +"', '"+ dvlatatude +"'), 4326))";
					st.executeUpdate(sqlDelCaThe);
            } else if(dvlatatude == 0 && dvid != 0){
            	//xoa loai thuc vat
            	String sqlDelLoai;
            	sqlDelLoai = "DELETE FROM dv_vitri WHERE id = "+ dvid +"";
            		st.executeUpdate(sqlDelLoai);
            	sqlDelLoai = "DELETE FROM dongvat WHERE id = "+ dvid +"";
            		st.executeUpdate(sqlDelLoai);
            } else {
            	//xoa ho thuc vat
            	String sqlDelHo;
            	sqlDelHo = "DELETE FROM dv_vitri WHERE id IN (SELECT id FROM dongvat WHERE ho like '"+ dvho +"')";
            		st.executeUpdate(sqlDelHo);
            	sqlDelHo = "DELETE FROM dongvat WHERE id IN (SELECT id FROM dongvat WHERE ho like '"+ dvho +"')";
            		st.executeUpdate(sqlDelHo);
            }
            
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void editThucvat(int tvid, String tvloai, String tvho,
			String tvchieucao, String tvduongkinh, String tvloaisinhtruong,
			String tvdangsong, String tvdongtay, String tvnambac,
			double tvlatatude, double tvlatatudeold, double tvlongatude,
			double tvlongatudeold, boolean tvgtgo, boolean tvgtcanh,
			boolean tvgtthuoc, boolean tvgtvlxaydung,
			boolean tvgtthucongmynghe, boolean tvgtanduoc, boolean tvgtcaydoc,
			boolean tvgtchotinhdau, boolean tvgtnhua, boolean tvgtsoi,
			boolean tvgthuocnhuom, boolean tvgtcongdungkhac, String tvdacdiem, String tvimage)
			throws SQLException {
		connect();
		try {
            st = con.createStatement();
            String sqlDoTanChe = "UPDATE tv_dotanche SET dongtay = "+ Integer.parseInt(tvdongtay.trim()) 
            		+", nambac = "+ Integer.parseInt(tvnambac.trim()) +" WHERE id = (SELECT dotanche FROM thucvat WHERE id = "+ tvid +") ";
			String sqlGiaTri = "UPDATE tv_giatri SET go = "
					+ tvgtgo + ", canh = " + tvgtcanh + ", thuoc = " + tvgtthuoc + ", vlxaydung = "
					+ tvgtvlxaydung + ", thucongmynghe = " + tvgtthucongmynghe + ",anduoc = "
					+ tvgtanduoc + ", caydoc = " + tvgtcaydoc + ", chotindau = " + tvgtchotinhdau
					+ ",nhua = " + tvgtnhua + ",soi = " + tvgtsoi + ",thuocnhuom = " + tvgthuocnhuom
					+ ",congdungkhac = " + tvgtcongdungkhac + " WHERE id = (SELECT giatri FROM thucvat WHERE id = "+ tvid +") ";
			int chieucao = 0;
			if("Cây tái sinh".equals(tvchieucao)) chieucao = 1;
			else if("Cây bụi".equals(tvchieucao)) chieucao = 2;
			else if("Dưới tán".equals(tvchieucao)) chieucao = 3;
			else if("Tán".equals(tvchieucao)) chieucao = 4;
			else if("Trên tán".equals(tvchieucao)) chieucao = 5;
			String sqlThucVat = "UPDATE thucvat SET loai = '" + tvloai
					+ "',ho = '" + tvho + "', chieucao = " + chieucao + ", duongkinh = '" + tvduongkinh + "',loaisinhtruong = '" + tvloaisinhtruong
					+ "', dangsong = '" + tvdangsong + "',dacdiem = '"+ tvdacdiem + "',image = '"+ tvimage + "' WHERE id = "+ tvid +" ";
			String sqlvitri = "UPDATE tv_vitri SET vitri = (ST_SetSRID(ST_MakePoint('" + tvlongatude + "', '" + tvlatatude
					+ "'), 4326)) WHERE vitri = (ST_SetSRID(ST_MakePoint('" + tvlongatudeold + "', '" + tvlatatudeold + "'), 4326))";
            st.executeUpdate(sqlDoTanChe);
            st.executeUpdate(sqlGiaTri);
            st.executeUpdate(sqlThucVat);
            st.executeUpdate(sqlvitri);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}
	
	public void editDongvat(int dvid, String dvloai, String dvho,
			String dvgioi, String dvnganh, String dvlop, String dvbo,
			String dvchi, String dvthucan, String dvdacdiem, double dvlatatude,
			double dvlatatudeold, double dvlongatude, double dvlongatudeold,
			boolean dvnstrencan, boolean dvnstrencay, boolean dvnsduoinuoc, String dvimage)
			throws SQLException {
		connect();
		try {
            st = con.createStatement();
            String sqlNoiSong = "UPDATE dv_noisong SET trencan = "+ dvnstrencan
            		+", trencay = "+ dvnstrencay +", duoinuoc = "+ dvnsduoinuoc +" WHERE id = (SELECT noisong FROM dongvat WHERE id = "+ dvid +") ";
			String sqlDongVat = "UPDATE dongvat SET gioi = '" + dvgioi
					+ "',nganh = '" + dvnganh + "', lop = '" + dvlop + "', bo = '" + dvbo + "', ho = '" + dvho + "', chi = '" + dvchi
					+ "', loai = '" + dvloai + "',dacdiem = '"+ dvdacdiem + "', thucan = '"+ dvthucan + "', image = '"+ dvimage + "' WHERE id = "+ dvid +" ";
			String sqlvitri = "UPDATE dv_vitri SET vitri = (ST_SetSRID(ST_MakePoint('" + dvlongatude + "', '" + dvlatatude
					+ "'), 4326)) WHERE vitri = (ST_SetSRID(ST_MakePoint('" + dvlongatudeold + "', '" + dvlatatudeold + "'), 4326))";
            st.executeUpdate(sqlNoiSong);
            st.executeUpdate(sqlDongVat);
            st.executeUpdate(sqlvitri);
		} catch (SQLException e) {
			System.out.println("Can't database excuting");
		} finally{
			closeConnect();
		}
	}

}
